// 查询
router.get('/search2', (req, res) => {
    let curr = Number(req.query.curr) - 1
    let limit = Number(req.query.limit);

    let { username, phone, inputtime, source } = req.query;
    console.log('查询参数：', curr, limit, username, phone, inputtime, source)

    if (typeof inputtime !== 'string') {
        res.json({ code: -1, msg: "日期格式不对" });
        return;
    }

    if (typeof username !== 'string') {
        res.json({ code: -1, msg: "姓名格式不对" });
        return;
    }

    // 要对长度加以限制，防止提交过多的内容
    if (username.length > 16) {
        res.json({ code: -1, msg: "姓名长度应小于等于16位" });
        return;
    }

    if (typeof phone !== 'string') {
        res.json({ code: -1, msg: "手机号码格式不对" });
        return;
    }

    if (typeof source !== 'string') {
        res.json({ code: -1, msg: "来源格式不正确" });
        return;
    }

    if (!['0', '1', '2', '3', '4'].includes(source)) {
        res.json({ code: -1, msg: "来源格式不正确" });
        return;
    }

    if (source == '0') {
        source = ""
    }

    if (inputtime == "") {
        db.query("select count(username) AS total_num from koubei WHERE ( username LIKE CONCAT('%',?,'%') OR username IS NULL ) AND ( phone LIKE CONCAT('%',?,'%') OR phone IS NULL ) AND ( source LIKE CONCAT('%',?,'%') OR source IS NULL )", [username, phone, source], function (err, result) {
            if (err) throw err;
            let total = result[0].total_num
            db.query("SELECT * FROM	koubei WHERE ( username LIKE CONCAT('%',?,'%') OR username IS NULL ) AND ( phone LIKE CONCAT('%',?,'%') OR phone IS NULL ) AND ( source LIKE CONCAT('%',?,'%') OR source IS NULL ) ORDER BY id DESC LIMIT ?,?", [username, phone, source, curr * limit, limit], function (err, result) {
                if (err) throw err;
                console.log('查询结果：', result)
                res.send({ code: 0, msg: 'success', total: total, list: result })
            })
        })

    } else {
        //  日期范围似乎无法使用like,所以对日期是否传值分开判断处理
        db.query("select count(username) AS total_num from koubei WHERE ( username LIKE CONCAT('%',?,'%') OR username IS NULL ) AND ( phone LIKE CONCAT('%',?,'%') OR phone IS NULL ) AND ( source LIKE CONCAT('%',?,'%') OR source IS NULL ) AND (inputtime between ? and ?) ", [username, phone, source, inputtime[0], inputtime[1]], function (err, result) {
            if (err) throw err;
            let total = result[0].total_num
            db.query("SELECT * FROM	koubei WHERE ( username LIKE CONCAT('%',?,'%') OR username IS NULL ) AND ( phone LIKE CONCAT('%',?,'%') OR phone IS NULL ) AND ( source LIKE CONCAT('%',?,'%') OR source IS NULL ) AND (inputtime between ? and ?) ORDER BY id DESC LIMIT ?,?", [username, phone, source, inputtime[0], inputtime[1], curr * limit, limit], function (err, result) {
                if (err) throw err;
                console.log('查询结果：', result)
                res.send({ code: 0, msg: 'success', total: total, list: result })
            })
        })

    }

})